﻿using System;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

namespace Shuba.Data
{
    public class ChannelMapping
    {
        public ChannelMapping()
        { }

        public List<Shuba.Model.ChannelMappingInfo> GetList(string ChannelName)
        {
            List<Shuba.Model.ChannelMappingInfo> _list = new List<Shuba.Model.ChannelMappingInfo>();
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"SELECT PRN_CODE,A.CHANNEL_ID,CHANNEL_NAME,
                                A.PROSPECT_TYPE ,C.MS_STD_NAME PROSPECT_NAME,
                                A.DEBTOR_TYPE ,D.MS_STD_NAME DEBTOR_NAME, EFFECTIVE_DATE,
                                A.ENTRY_BY,A.ENTRY_DATE,A.UPDATE_BY,A.UPDATE_DATE, A.IS_ACTIVE
                            FROM TBL_MS_CHANNEL_MAPPING A INNER JOIN TBL_MS_CHANNEL B
                                ON A.CHANNEL_ID = B.CHANNEL_ID INNER JOIN 
                                ( SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE 
	                                MS_STD_GRP_ID = 5) C
                                ON A.PROSPECT_TYPE = C.MS_STD_ID INNER JOIN
                                ( SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE 
	                                MS_STD_GRP_ID = 9) D
                                ON A.DEBTOR_TYPE = D.MS_STD_ID
                            WHERE UPPER(CHANNEL_NAME) LIKE '%" + ChannelName.ToUpper() + "%'";

            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);
            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    _list.Add(new Shuba.Model.ChannelMappingInfo(mydr.GetString(0), mydr.GetString(1), mydr.GetString(2), mydr.GetString(3), mydr.GetString(4), mydr.GetString(5), mydr.GetString(6), mydr.GetDateTime(7), mydr.GetString(8), mydr.GetDateTime(9), mydr.GetString(10), mydr.GetDateTime(11), mydr.GetString(12)));
                }
                mydr.Close();
            }
            return _list;
        }

        public Shuba.Model.ChannelMappingInfo Get(string CHANNEL_ID, string PROSPECT_TYPE, string DEBTOR_TYPE)
        {
            Shuba.Model.ChannelMappingInfo bi = new Shuba.Model.ChannelMappingInfo();
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"SELECT PRN_CODE,A.CHANNEL_ID,CHANNEL_NAME,
                                  A.PROSPECT_TYPE ,C.MS_STD_NAME PROSPECT_NAME,
                                  A.DEBTOR_TYPE ,D.MS_STD_NAME DEBTOR_NAME, EFFECTIVE_DATE,
                                  A.ENTRY_BY,A.ENTRY_DATE,A.UPDATE_BY, A.UPDATE_DATE, A.IS_ACTIVE
                            FROM TBL_MS_CHANNEL_MAPPING A INNER JOIN TBL_MS_CHANNEL B
                                  ON A.CHANNEL_ID = B.CHANNEL_ID INNER JOIN 
                                  ( SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE 
            	                    MS_STD_GRP_ID = 5) C
                                    ON A.PROSPECT_TYPE = C.MS_STD_ID INNER JOIN
                                  ( SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD WHERE 
            	                    MS_STD_GRP_ID = 9) D
                                    ON A.DEBTOR_TYPE = D.MS_STD_ID
                                    WHERE RTRIM(LTRIM(A.CHANNEL_ID)) = @CHANNEL_ID 
	                                    AND RTRIM(LTRIM(A.PROSPECT_TYPE)) = @PROSPECT_TYPE
	                                    AND RTRIM(LTRIM(A.DEBTOR_TYPE)) = @DEBTOR_TYPE";
            DbCommand mycmd = mydb.GetSqlStringCommand(mysql);
            mydb.AddInParameter(mycmd, "@CHANNEL_ID", DbType.String, CHANNEL_ID.Trim());
            mydb.AddInParameter(mycmd, "@PROSPECT_TYPE", DbType.String, PROSPECT_TYPE.Trim());
            mydb.AddInParameter(mycmd, "@DEBTOR_TYPE", DbType.String, DEBTOR_TYPE.Trim());

            using (IDataReader mydr = mydb.ExecuteReader(mycmd))
            {
                while (mydr.Read())
                {
                    bi = new Shuba.Model.ChannelMappingInfo(mydr.GetString(0), mydr.GetString(1), mydr.GetString(2), mydr.GetString(3), mydr.GetString(4), mydr.GetString(5), mydr.GetString(6), mydr.GetDateTime(7), mydr.GetString(8), mydr.GetDateTime(9), mydr.GetString(10), mydr.GetDateTime(11), mydr.GetString(12));
                }
                mydr.Close();
            }
            return bi;
        }

        public int Save(Shuba.Model.ChannelMappingInfo bi)
        {
            Database mydb = DBConnection.CreateDatabase();

            DbCommand mycomm = mydb.GetSqlStringCommand(@"UPDATE TBL_MS_CHANNEL_MAPPING_TMP SET 
                                PRN_CODE = @PRN_CODE,
                                CHANNEL_ID = @CHANNEL_ID,
                                PROSPECT_TYPE = @PROSPECT_TYPE,
                                DEBTOR_TYPE = @DEBTOR_TYPE,
                                EFFECTIVE_DATE = @EFFECTIVE_DATE,
                                ACT_TYPE = @ACT_TYPE,
                                ENTRY_BY = @ENTRY_BY,
                                ENTRY_DATE = @ENTRY_DATE,
                                UPDATE_BY = @UPDATE_BY,
                                UPDATE_DATE = @UPDATE_DATE,
                                CHANNEL_ID_ORIG = @CHANNEL_ID_ORIG,
                                PROSPECT_TYPE_ORIG = @PROSPECT_TYPE_ORIG,
                                DEBTOR_TYPE_ORIG = @DEBTOR_TYPE_ORIG,
                                IS_ACTIVE = @IS_ACTIVE
                                WHERE LTRIM(RTRIM(CHANNEL_ID)) = LTRIM(RTRIM(@CHANNEL_ID)) AND LTRIM(RTRIM(PROSPECT_TYPE)) = LTRIM(RTRIM(@PROSPECT_TYPE)) AND LTRIM(RTRIM(DEBTOR_TYPE)) = LTRIM(RTRIM(@DEBTOR_TYPE))
            	            IF @@ROWCOUNT = 0
                            INSERT INTO TBL_MS_CHANNEL_MAPPING_TMP
                                (PRN_CODE,CHANNEL_ID,
                                 PROSPECT_TYPE,DEBTOR_TYPE,
                                 EFFECTIVE_DATE,ACT_TYPE,
                                 ENTRY_BY,ENTRY_DATE,
                                 UPDATE_BY,UPDATE_DATE,CHANNEL_ID_ORIG,
                                 PROSPECT_TYPE_ORIG,DEBTOR_TYPE_ORIG,IS_ACTIVE) 
                          VALUES(LEFT(@PRN_CODE,2),@CHANNEL_ID,
                                @PROSPECT_TYPE,@DEBTOR_TYPE,
                                @EFFECTIVE_DATE,@ACT_TYPE,
                                @ENTRY_BY,@ENTRY_DATE,@UPDATE_BY,
                                @UPDATE_DATE,@CHANNEL_ID_ORIG,
                                @PROSPECT_TYPE_ORIG,@DEBTOR_TYPE_ORIG,@IS_ACTIVE)");

            mydb.AddInParameter(mycomm, "@PRN_CODE", DbType.String, bi.PRN_CODE.ToUpper());
            mydb.AddInParameter(mycomm, "@CHANNEL_ID", DbType.String, bi.CHANNEL_ID.ToUpper());
            mydb.AddInParameter(mycomm, "@PROSPECT_TYPE", DbType.String, bi.PROSPECT_TYPE.ToUpper());
            mydb.AddInParameter(mycomm, "@DEBTOR_TYPE", DbType.String, bi.DEBTOR_TYPE.ToUpper());
            mydb.AddInParameter(mycomm, "@EFFECTIVE_DATE", DbType.DateTime, bi.EFFECTIVE_DATE);
            mydb.AddInParameter(mycomm, "@ACT_TYPE", DbType.String, bi.ACT_TYPE);
            mydb.AddInParameter(mycomm, "@ENTRY_BY", DbType.String, bi.ENTRY_BY);
            mydb.AddInParameter(mycomm, "@ENTRY_DATE", DbType.DateTime, bi.ENTRY_DATE);
            mydb.AddInParameter(mycomm, "@UPDATE_BY", DbType.String, bi.UPDATE_BY);
            mydb.AddInParameter(mycomm, "@UPDATE_DATE", DbType.DateTime, bi.UPDATE_DATE);
            mydb.AddInParameter(mycomm, "@CHANNEL_ID_ORIG", DbType.String, bi.CHANNEL_ID_ORIG);
            mydb.AddInParameter(mycomm, "@PROSPECT_TYPE_ORIG", DbType.String, bi.PROSPECT_TYPE_ORIG);
            mydb.AddInParameter(mycomm, "@DEBTOR_TYPE_ORIG", DbType.String, bi.DEBTOR_TYPE_ORIG);
            mydb.AddInParameter(mycomm, "@IS_ACTIVE", DbType.String, bi.IS_ACTIVE);

            return mydb.ExecuteNonQuery(mycomm);
        }

        public List<Shuba.Model.ChannelMappingInfo> GetListApproval(string UpdateBy)
        {
            List<Shuba.Model.ChannelMappingInfo> _channel = new List<Shuba.Model.ChannelMappingInfo>();

            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"SELECT PRN_CODE,
                                COALESCE(CHANNEL_ID,NULL,'') CHANNEL_ID,
                                PROSPECT_TYPE, DEBTOR_TYPE,
                                EFFECTIVE_DATE, ENTRY_BY, 
                                ENTRY_DATE,UPDATE_BY,UPDATE_DATE,
                                ACT_TYPE, CHANNEL_ID_ORIG, PROSPECT_TYPE_ORIG, DEBTOR_TYPE_ORIG, IS_ACTIVE
                            FROM TBL_MS_CHANNEL_MAPPING_TMP
                                WHERE (RTRIM(UPDATE_BY) <> '" + UpdateBy + "') ORDER BY CHANNEL_ID";
            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);

            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    _channel.Add(new Shuba.Model.ChannelMappingInfo(mydr.GetString(0), mydr.GetString(1), mydr.GetString(2), mydr.GetString(3), mydr.GetDateTime(4), mydr.GetString(5), mydr.GetDateTime(6), mydr.GetString(7), mydr.GetDateTime(8), mydr.GetString(9), mydr.GetString(10), mydr.GetString(11), mydr.GetString(12), mydr.GetString(13)));
                }
                mydr.Close();
            }
            return _channel;
        }

        public Shuba.Model.ChannelMappingInfo GetApprovalByID(string CHANNEL_ID, string PROSPECT_TYPE, string DEBTOR_TYPE)
        {
            Shuba.Model.ChannelMappingInfo _list = new Shuba.Model.ChannelMappingInfo();
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"SELECT PRN_CODE,
                                COALESCE(CHANNEL_ID,NULL,'')CHANNEL_ID,
                                PROSPECT_TYPE, DEBTOR_TYPE ,
                                EFFECTIVE_DATE, ENTRY_BY,
                                ENTRY_DATE, UPDATE_BY,
                                UPDATE_DATE, ACT_TYPE, CHANNEL_ID_ORIG, PROSPECT_TYPE_ORIG, DEBTOR_TYPE_ORIG, IS_ACTIVE
                             FROM TBL_MS_CHANNEL_MAPPING_TMP
                                WHERE RTRIM(LTRIM(CHANNEL_ID)) = @CHANNEL_ID 
	                            AND RTRIM(LTRIM(PROSPECT_TYPE)) = @PROSPECT_TYPE
	                            AND RTRIM(LTRIM(DEBTOR_TYPE)) = @DEBTOR_TYPE";
            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);
            mydb.AddInParameter(mycomm, "@CHANNEL_ID", DbType.String, CHANNEL_ID.Trim());
            mydb.AddInParameter(mycomm, "@PROSPECT_TYPE", DbType.String, PROSPECT_TYPE.Trim());
            mydb.AddInParameter(mycomm, "@DEBTOR_TYPE", DbType.String, DEBTOR_TYPE.Trim());

            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    _list = new Shuba.Model.ChannelMappingInfo(mydr.GetString(0), mydr.GetString(1), mydr.GetString(2), mydr.GetString(3), mydr.GetDateTime(4), mydr.GetString(5), mydr.GetDateTime(6), mydr.GetString(7), mydr.GetDateTime(8), mydr.GetString(9), mydr.GetString(10), mydr.GetString(11), mydr.GetString(12), mydr.GetString(13));
                }
                mydr.Close();
            }
            return _list;
        }

        public int Approve(string ActType,string ChannelID, string PrspctType, string DbtrType)
        {
            int success = 0;
            try
            {
                Database mydb = DBConnection.CreateDatabase();
                DbCommand mycomm = mydb.GetStoredProcCommand("PROC_APPROVAL_CHANNEL_MAPPING");
                mydb.AddInParameter(mycomm, "@ACTTYPE", DbType.String, ActType);
                mydb.AddInParameter(mycomm, "@ChannelID", DbType.String, ChannelID);
                mydb.AddInParameter(mycomm, "@PrspctType", DbType.String, PrspctType);
                mydb.AddInParameter(mycomm, "@DbtrType", DbType.String, DbtrType);

                success = mydb.ExecuteNonQuery(mycomm);
            }
            catch (Exception) { }
            return success;
        }

        public void Reject(string CHANNEL_ID, string PROSPECT_TYPE, string DEBTOR_TYPE)
        {
            Database mydb = DBConnection.CreateDatabase();

            string mysql =@"DELETE FROM TBL_MS_CHANNEL_MAPPING_TMP  WHERE RTRIM(LTRIM(CHANNEL_ID)) = @CHANNEL_ID 
	                            AND RTRIM(LTRIM(PROSPECT_TYPE)) = @PROSPECT_TYPE
	                            AND RTRIM(LTRIM(DEBTOR_TYPE)) = @DEBTOR_TYPE";
            DbCommand mycmd = mydb.GetSqlStringCommand(mysql);
            mydb.AddInParameter(mycmd, "@CHANNEL_ID", DbType.String, CHANNEL_ID.Trim());
            mydb.AddInParameter(mycmd, "@PROSPECT_TYPE", DbType.String, PROSPECT_TYPE.Trim());
            mydb.AddInParameter(mycmd, "@DEBTOR_TYPE", DbType.String, DEBTOR_TYPE.Trim());

            mydb.ExecuteNonQuery(mycmd);
        }


        public static bool CheckExistingCombination(string ChannelID, string ProspectType, string DebtorType)
        {
            bool isExist = false;

            Database mydb = DBConnection.CreateDatabase();
            DbCommand mycomm = mydb.GetSqlStringCommand(@"  SELECT PRN_CODE,CHANNEL_ID,PROSPECT_TYPE,DEBTOR_TYPE FROM TBL_MS_CHANNEL_MAPPING
                                                                WHERE CHANNEL_ID=@CHANNEL_ID AND PROSPECT_TYPE=@PROSPECT_TYPE AND DEBTOR_TYPE=@DEBTOR_TYPE 
                                                            UNION 
                                                            SELECT PRN_CODE,CHANNEL_ID,PROSPECT_TYPE,DEBTOR_TYPE FROM TBL_MS_CHANNEL_MAPPING_TMP
                                                                WHERE CHANNEL_ID=@CHANNEL_ID AND PROSPECT_TYPE=@PROSPECT_TYPE AND DEBTOR_TYPE=@DEBTOR_TYPE");
            mydb.AddInParameter(mycomm, "@CHANNEL_ID", DbType.String, ChannelID);
            mydb.AddInParameter(mycomm, "@PROSPECT_TYPE", DbType.String, ProspectType);
            mydb.AddInParameter(mycomm, "@DEBTOR_TYPE", DbType.String, DebtorType);
            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    isExist = true;
                }
                mydr.Close();
            }
            return isExist;
        }

        public static bool CheckExistingCombination2(string ChannelID, string ProspectType, string DebtorType)
        {
            bool isExist = false;

            Database mydb = DBConnection.CreateDatabase();
            DbCommand mycomm = mydb.GetSqlStringCommand(@"  SELECT PRN_CODE,CHANNEL_ID,PROSPECT_TYPE,DEBTOR_TYPE FROM TBL_MS_CHANNEL_MAPPING
                                                                WHERE CHANNEL_ID=@CHANNEL_ID AND PROSPECT_TYPE=@PROSPECT_TYPE AND DEBTOR_TYPE=@DEBTOR_TYPE AND IS_ACTIVE = '1'");
            mydb.AddInParameter(mycomm, "@CHANNEL_ID", DbType.String, ChannelID);
            mydb.AddInParameter(mycomm, "@PROSPECT_TYPE", DbType.String, ProspectType);
            mydb.AddInParameter(mycomm, "@DEBTOR_TYPE", DbType.String, DebtorType);
            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    isExist = true;
                }
                mydr.Close();
            }
            return isExist;
        }

        public static bool CheckCombinationExternalMapping(string EXTERNAL_ID, string CHANNEL_ID)
        {
            bool isExist = false;

            Database mydb = DBConnection.CreateDatabase();
            DbCommand mycomm = mydb.GetSqlStringCommand(@"SELECT * FROM TBL_MS_EXTERNAL_MAPPING
                                                                WHERE EXTERNAL_ID=@EXTERNAL_ID AND CHANNEL_ID=@CHANNEL_ID ");
            mydb.AddInParameter(mycomm, "@EXTERNAL_ID", DbType.String, EXTERNAL_ID);
            mydb.AddInParameter(mycomm, "@CHANNEL_ID", DbType.String, CHANNEL_ID);
            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    isExist = true;
                }
                mydr.Close();
            }
            return isExist;
        }

        //jika sudah ada crc code maka act_type jadi edit
        //jika tidak ada crc code maka cek 3 kombinasi
        public static bool CheckExistingCombinationForUpload(string crcCode, string ChannelID, string ProspectType, string DebtorType)
        {
            bool isExist = false;

            Database mydb = DBConnection.CreateDatabase();
            DbCommand mycomm = mydb.GetSqlStringCommand(@"IF NOT EXISTS (SELECT PRN_CODE,CHANNEL_ID,PROSPECT_TYPE,DEBTOR_TYPE FROM TBL_MS_CHANNEL_MAPPING
                                                                WHERE PRN_CODE=@PRN_CODE AND CHANNEL_ID=@CHANNEL_ID AND PROSPECT_TYPE=@PROSPECT_TYPE AND DEBTOR_TYPE=@DEBTOR_TYPE 
                                                            UNION 
                                                            SELECT PRN_CODE,CHANNEL_ID,PROSPECT_TYPE,DEBTOR_TYPE FROM TBL_MS_CHANNEL_MAPPING_TMP
                                                                WHERE PRN_CODE=@PRN_CODE AND CHANNEL_ID=@CHANNEL_ID AND PROSPECT_TYPE=@PROSPECT_TYPE AND DEBTOR_TYPE=@DEBTOR_TYPE)

                                                            SELECT PRN_CODE,CHANNEL_ID,PROSPECT_TYPE,DEBTOR_TYPE FROM TBL_MS_CHANNEL_MAPPING
                                                                WHERE CHANNEL_ID=@CHANNEL_ID AND PROSPECT_TYPE=@PROSPECT_TYPE AND DEBTOR_TYPE=@DEBTOR_TYPE 
                                                            UNION 
                                                            SELECT PRN_CODE,CHANNEL_ID,PROSPECT_TYPE,DEBTOR_TYPE FROM TBL_MS_CHANNEL_MAPPING_TMP
                                                                WHERE CHANNEL_ID=@CHANNEL_ID AND PROSPECT_TYPE=@PROSPECT_TYPE AND DEBTOR_TYPE=@DEBTOR_TYPE");
            mydb.AddInParameter(mycomm, "@PRN_CODE", DbType.String, crcCode);
            mydb.AddInParameter(mycomm, "@CHANNEL_ID", DbType.String, ChannelID);
            mydb.AddInParameter(mycomm, "@PROSPECT_TYPE", DbType.String, ProspectType);
            mydb.AddInParameter(mycomm, "@DEBTOR_TYPE", DbType.String, DebtorType);
            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    isExist = true;
                }
                mydr.Close();
            }
            return isExist;
        }

        public static bool CheckIsUsedPRN(string CHANNEL_ID, string PROSPECT_TYPE, string DEBTOR_TYPE)
        {
            bool isExist = false;

            Database mydb = DBConnection.CreateDatabase();
            DbCommand mycomm = mydb.GetSqlStringCommand(@"SELECT TOP 1 * FROM TBL_PROSPECT 
                        WHERE LTRIM(RTRIM(CHANNELING))=@CHANNELING AND LTRIM(RTRIM(PROSPECT_TYPE))=@PROSPECT_TYPE AND LTRIM(RTRIM(DEBTOR_TYPE))=@DEBTOR_TYPE");
            mydb.AddInParameter(mycomm, "@CHANNELING", DbType.String, CHANNEL_ID.Trim());
            mydb.AddInParameter(mycomm, "@PROSPECT_TYPE", DbType.String, PROSPECT_TYPE.Trim());
            mydb.AddInParameter(mycomm, "@DEBTOR_TYPE", DbType.String, DEBTOR_TYPE.Trim());
            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    isExist = true;
                }
                mydr.Close();
            }
            return isExist;
        }

        public bool CekExistingChannelDebtorProspectType(string CHANNEL_ID, string PROSPECT_TYPE, string DEBTOR_TYPE)
        {
            bool isExist = false;
            Database mydb = DBConnection.CreateDatabase();
            DbCommand mycomm = mydb.GetSqlStringCommand(@"SELECT TOP 1 * FROM TBL_MS_CHANNEL WHERE LTRIM(RTRIM(CHANNEL_ID))=@CHANNEL_ID");
            mydb.AddInParameter(mycomm, "@CHANNEL_ID", DbType.String, CHANNEL_ID.Trim());
            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    isExist = true;
                }
                mydr.Close();
            }
            if (!isExist) return false;
            isExist = false;

            mycomm = mydb.GetSqlStringCommand(@"SELECT TOP 1 * FROM TBL_MS_STANDARD WHERE (LTRIM(RTRIM(MS_STD_ID))=@MS_STD_ID) AND (MS_STD_GRP_ID=5) ORDER BY MS_STD_ID");
            mydb.AddInParameter(mycomm, "@MS_STD_ID", DbType.String, PROSPECT_TYPE.Trim());
            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    isExist = true;
                }
                mydr.Close();
            }
            if (!isExist) return false;
            isExist = false;

            mycomm = mydb.GetSqlStringCommand(@"SELECT TOP 1 * FROM TBL_MS_STANDARD WHERE (LTRIM(RTRIM(MS_STD_ID))=@MS_STD_ID) AND (MS_STD_GRP_ID=9) ORDER BY MS_STD_ID");
            mydb.AddInParameter(mycomm, "@MS_STD_ID", DbType.String, DEBTOR_TYPE.Trim());
            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    isExist = true;
                }
                mydr.Close();
            }
            return isExist;
        }

    }
}
